package com.li.commons.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * excel操作相关方法
 * 
 * @author huqilong
 * 
 */
public class ExcelUtil {

	/**
	 * 根据输入流获取excel文档对象
	 * 
	 * @param inputStream
	 * @return
	 */
	public static Workbook getWorkbook(InputStream inputStream) {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 * 根据文件路径获取excel文档对象
	 * 
	 * @param file
	 * @return
	 */
	public static Workbook getWorkbook(String file) {
		Workbook wb = null;
		try {
			File f = new File(file);
			wb = WorkbookFactory.create(f);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
		return wb;
	}

	/**
	 * 根据文件路径加载excel数据
	 * 
	 * @param file
	 * @param sheetindex
	 * @return
	 */
	public static List<Object[]> loadData(String file, int sheetindex) {
		Workbook wb = getWorkbook(file);
		return loadData(wb, sheetindex);
	}

	/**
	 * 根据输入流加载数据
	 * 
	 * @param inputStream
	 * @param sheetindex
	 * @return
	 */
	public static List<Object[]> loadData(InputStream inputStream,
			int sheetindex) {
		Workbook workbook = getWorkbook(inputStream);
		return loadData(workbook, sheetindex);
	}

	/**
	 * 获取单元格内容
	 * 
	 * @param cell
	 * @return
	 */
	private static Object getCellValue(Cell cell) {
		Object value = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
				if (value != null) {
					value = DateTimeUtil.toString((Date) value, "yyyy-MM-dd");
				}
			} else {
				value = new BigDecimal(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().toString();
			break;
		case Cell.CELL_TYPE_FORMULA:
			value = String.valueOf(cell.getNumericCellValue());
			if (value != null && value.equals("NaN")) {
				// 如果获取的数据值为非法值,则转换为获取字符串
				value = cell.getRichStringCellValue().toString();
			}
			break;
		case Cell.CELL_TYPE_ERROR:
			// 故障
			value = "";
			break;
		default:
			value = cell.getRichStringCellValue().toString();
		}
		return value;
	}

	/**
	 * 加载指定文档某个sheet的数据
	 * 
	 * @param wb
	 * @param sheetindex
	 * @return
	 */
	private static List<Object[]> loadData(Workbook wb, int sheetindex) {
		List<Object[]> dataList = new ArrayList<Object[]>();
		Sheet sheet = wb.getSheetAt(sheetindex);
		int rows = sheet.getPhysicalNumberOfRows();
		for (int i = 1; i < rows; i++) {
			try {
				Row row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				Integer cols = new Integer(row.getLastCellNum());
				if (cols == null || cols <= 0) {
					continue;
				}
				Object[] objectArray = new Object[cols];
				for (int j = 0; j < objectArray.length; j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						objectArray[j] = getCellValue(cell);
					} else {
						objectArray[j] = null;
					}
				}
				dataList.add(objectArray);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return dataList;
	}
	
	public static List<Object[]> loadDataFrom0(InputStream inputStream,int sheetindex) {
		Workbook workbook = getWorkbook(inputStream);
		return loadDataFrom0(workbook, sheetindex);
	}
	
	/**
	 * 加载所有行数据信息
	 * @param wb
	 * @param sheetindex
	 * @return
	 */
	private static List<Object[]> loadDataFrom0(Workbook wb, int sheetindex) {
		List<Object[]> dataList = new ArrayList<Object[]>();
		Sheet sheet = wb.getSheetAt(sheetindex);
		int firstRowNum = sheet.getFirstRowNum();
		int rows = sheet.getPhysicalNumberOfRows();
		for (int i = 0; i < firstRowNum+rows; i++) {
			try {
				Row row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				Integer cols = new Integer(row.getLastCellNum());
				if (cols == null || cols <= 0) {
					continue;
				}
				Object[] objectArray = new Object[cols];
				for (int j = 0; j < objectArray.length; j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						objectArray[j] = getCellValue(cell);
					} else {
						objectArray[j] = null;
					}
				}
				dataList.add(objectArray);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return dataList;
	}

	/**
	 * 往某一行里写入数据
	 * @param sheet
	 * @param rowIndex
	 * @param column
	 * @param value
	 */
	public static void writeData(Sheet sheet, Integer rowIndex, Integer column, String value) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		Cell cell = row.getCell(column);
		if (cell == null) {
			cell = row.createCell(column);
		}
		cell.setCellValue(value);
	}
	
	/**
	 * 往某一行里写入数据
	 * @param sheet
	 * @param rowIndex
	 * @param column
	 * @param value
	 */
	public static void writeData(Sheet sheet, Integer rowIndex, Integer column, String value, CellStyle cellStyle) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		Cell cell = row.getCell(column);
		if (cell == null) {
			cell = row.createCell(column);
		}
		if(cellStyle!=null){
			cell.setCellStyle(cellStyle);
		}
		cell.setCellValue(value);
	}
	
	public static void main(String args[]){
		List<Object[]> dataList = loadData("/Users/huqilong/Desktop/订单状态.xlsx", 0);
		for(int i = 0 ;i<dataList.size();i++){
			Object[] data = dataList.get(i);
			String ordersNo=data[0].toString();
			String ordersState=data[1].toString();
			System.out.println("update decorate_order set order_states="+ordersState+" where orders_no='"+ordersNo+"';");
		}
	}
	
}